﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using CommonLibrary.DB;
using CommonLibrary.DataModel.Basic;
using System.Data.SqlClient;

namespace ServiceApplication.Dao.Basic
{
    public class SalaDAO
    {
        public DataTable QueryUsersInfo()
        {
            String sql = "SELECT [dept_name] as 部门名称, [staff_name] as 用户名称, [staff_id] as 用户编号 from jc_dept as de, jc_staff st where de.dept_id = st.dept_id";
            return DBExecuteUtil.querySqlTable(sql);
        }

        public DataTable QuerySalaryInfo()
        {
            String sql = "SELECT [salary_list_id] as 提成编号, [dept_name] as 部门名称, [post_name] as 岗位名称, [user_name] as 用户名称, [user_id] as 用户编号," +
                "[rank_name] as 级别, [base_salary] as 基本工资, [post_salary] as 岗位工资, [house_subsidy] as 住房补贴, [phone_subsidy] as 手机补贴, [summary] as 小计," +
                "[earn_extern] as 提成, [remark] as 备注 FROM staff_salary";

            return DBExecuteUtil.querySqlTable(sql);
        }

        public void UpdataOrInsertSalary(SalaEntity salaEntity)
        {
            var userId = salaEntity.User_Id;
            String sql = "SELECT * FROM staff_salary Where [user_id] = '" + userId + "'";
            var result = DBExecuteUtil.querySqlArray(sql);
            if (result != null && result.Length > 0)
            {
                sql = "UPDATE staff_salary SET [dept_name]=@dept_name,[post_name]=@post_name,[user_name]=@user_name,[user_id]=@user_id,[rank_name]=@rank_name,[base_salary]=@base_salary," +
                   "[post_salary]=@post_salary,[house_subsidy]=@house_subsidy,[phone_subsidy]=@phone_subsidy,[summary]=@summary,[earn_extern]=@earn_extern,[remark]=@remark";
            }
            else
            {
                sql = "INSERT INTO staff_salary([dept_name],[post_name],[user_name],[user_id],[rank_name],[base_salary],[post_salary],[house_subsidy],[phone_subsidy]," +
                "[summary],[earn_extern],[remark]) values (@dept_name,@post_name,@user_name,@user_id,@rank_name,@base_salary,@post_salary,@house_subsidy,@phone_subsidy,@summary,@earn_extern,@remark)";
            }

            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@dept_name", salaEntity.Dept_Name));
            parameterList.Add(new SqlParameter("@post_name", salaEntity.Post_Name));
            parameterList.Add(new SqlParameter("@user_name", salaEntity.User_Name));
            parameterList.Add(new SqlParameter("@user_id", salaEntity.User_Id));
            parameterList.Add(new SqlParameter("@rank_name", salaEntity.Rank_Name));
            parameterList.Add(new SqlParameter("@base_salary", salaEntity.Base_Salary));
            parameterList.Add(new SqlParameter("@post_salary", salaEntity.Post_Name));
            parameterList.Add(new SqlParameter("@house_subsidy", salaEntity.House_Subsidy));
            parameterList.Add(new SqlParameter("@phone_subsidy", salaEntity.Phone_Subsidy));
            parameterList.Add(new SqlParameter("@summary", salaEntity.Summary));
            parameterList.Add(new SqlParameter("@earn_extern", salaEntity.Earn_Extern));
            parameterList.Add(new SqlParameter("@remark", salaEntity.Remark));

            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
        }

        public DataTable QueryPostName()
        {
            String sql = "SELECT [post_name] as 岗位名称 FROM postforsalary";
            return DBExecuteUtil.querySqlTable(sql);
        }

        public DataTable QueryRankName()
        {
            String sql = "SELECT [rank_name] as 级别名称 FROM ranklforsalary";
            return DBExecuteUtil.querySqlTable(sql);
        }

        public Boolean AddPost(String postName)
        {
            var result = GetPost(postName);
            if (!result)
            {
                String sql = "INSERT INTO postforsalary ([post_name]) values (@postName)";
                List<SqlParameter> parameterList = new List<SqlParameter>();
                parameterList.Add(new SqlParameter("@postName", postName));
                DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
                return true;
            }
            else
                return false;
        }

        private Boolean GetPost(String postName)
        {

            String sql = "SELECT [post_name] FROM postforsalary WHERE [post_name]=@postName";
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@postName", postName));
            var dt = DBExecuteUtil.querySqlTable(sql, parameterList.ToArray());
            if (dt == null || dt.Rows.Count == 0)
                return false;
            else
                return true;
        }

        public Boolean AddRank(String rankName)
        {
            var result = GetRank(rankName);
            if (!result)
            {
                String sql = "INSERT INTO ranklforsalary ([rank_name]) values (@rankName)";
                List<SqlParameter> parameterList = new List<SqlParameter>();
                parameterList.Add(new SqlParameter("@rankName", rankName));
                DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
                return true;
            }
            else
                return false;
        }

        private Boolean GetRank(String rankName)
        {
            String sql = "SELECT [rank_name] FROM ranklforsalary WHERE [rank_name]=@rankname";
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@rankName", rankName));
            var dt = DBExecuteUtil.querySqlTable(sql, parameterList.ToArray());
            if (dt == null || dt.Rows.Count == 0)
                return false;
            else
                return true;
        }
    }
}
